import java.awt.Color;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DbHandler {

	private static String DB_URL = "jdbc:sqlite:C:/workspace/projectdb2.db";
	
	private static Connection conn;
	
	
	
	public DbHandler() {
	    
	    conn = null;
	    
	    try
	    {
			// load the sqlite-JDBC driver using the current class loader
	    	Class.forName("org.sqlite.JDBC");
		    
	    	// create a database connection
	    	conn = DriverManager.getConnection(DB_URL);     
	    }
	    catch(SQLException e)
	    {
	      // if the error message is "out of memory", 
	      // it probably means no database file is found
	      System.err.println(e.getMessage());
	    }
	    catch(Exception e) {
	    	System.out.println(e.getMessage());
	    }
	    finally
	    {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
	    
	}

	public ArrayList<AnswerData> getAllAnswers() {
		ArrayList<AnswerData> list = new ArrayList<AnswerData>();
		
		try {
			ResultSet rs = null;
			
			conn = DriverManager.getConnection(DB_URL);
			String stmt = "select * from answers";
			PreparedStatement statement = conn.prepareStatement(stmt);
		    
		    rs = statement.executeQuery();
		    
		    while(rs.next()){
		    	AnswerData result = new AnswerData();
		    	
		    	result.setId(rs.getInt("id"));
				result.setName(rs.getString("colorname"));
				System.out.println("Color: " + rs.getString("colorname"));
				
				int r = rs.getInt("r");
				int g = rs.getInt("g");
				int b = rs.getInt("b");
				Color rgb = new Color(r, g, b); 
				result.setRgb(rgb);
				
				result.setUserId(rs.getInt("user_id"));
		    	list.add(result);
		    }
		    
		}
		catch(SQLException e){
			System.out.println(e.getMessage());
		}
	    finally
	    {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
		
		return list;
	}
	
	public void getOneUserData(AnswerData answer){
		int id = answer.getId();
		
		try {
			ResultSet rs = null;
			conn = DriverManager.getConnection(DB_URL);
			
			String stmt = "select * from users where id = ?";
			PreparedStatement statement = conn.prepareStatement(stmt);
			
			statement.setInt(1, id);
			
			rs = statement.executeQuery();
			
			while(rs.next()){
				answer.setLanguage(rs.getString("language"));
				answer.setMonitor(rs.getString("monitor"));
			}
			
		}
		catch(Exception e){
			
		}
		finally {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
		
	}
	
	public ArrayList<AnswerData> getOneColorData(float hue, float saturation, float brightness){
		ArrayList<AnswerData> list = new ArrayList<AnswerData>();
		
		try {
			ResultSet rs = null;
			
			conn = DriverManager.getConnection(DB_URL);
			
			Color rgb = Color.getHSBColor(hue, saturation, brightness);
						
			String stmt = "select * from answers where r = ? AND g = ? AND b = ?";
			PreparedStatement statement = conn.prepareStatement(stmt);
		    statement.setInt(1, rgb.getRed());
		    statement.setInt(2, rgb.getGreen());
		    statement.setInt(3, rgb.getBlue());
		    
		    rs = statement.executeQuery();
		    
		    while(rs.next()){
		    	AnswerData result = new AnswerData();
		    	float[] hsb = {hue, saturation, brightness};
		    	result.setHsbVal(hsb);
		    	result.setId(rs.getInt("id"));
				result.setName(rs.getString("colorname"));
				System.out.println("Color: " + rs.getString("colorname"));
				result.setRgb(rgb);
				result.setUserId(rs.getInt("user_id"));
		    	list.add(result);
		    }
		    
		}
		catch(SQLException e){
			System.out.println(e.getMessage());
		}
	    finally
	    {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
		
		
		return list;
		
	}
	
	public ArrayList<AnswerData> getRangeColorData(float[] hsb1, float[] hsb2){
		ArrayList<AnswerData> list = new ArrayList<AnswerData>();
		
		try {
			ResultSet rs = null;
			
			conn = DriverManager.getConnection(DB_URL);
						
			String stmt = "select * from answers where hue >= ? AND hue <= ? AND brightness >= ? AND brightness <= ?";
			PreparedStatement statement = conn.prepareStatement(stmt);
		    statement.setFloat(1, min(hsb1[0], hsb2[0]));
		    statement.setFloat(2, max(hsb1[0], hsb2[0]));
		    statement.setFloat(3, min(hsb1[2], hsb2[2]));
		    statement.setFloat(4, max(hsb1[2], hsb2[2]));
		    
		    
		    rs = statement.executeQuery();
		    
		    while(rs.next()){
		    	AnswerData result = new AnswerData();
		    	
		    	int r = rs.getInt("r");
		    	int g = rs.getInt("g");
		    	int b = rs.getInt("b");
		    	Color rgb = new Color(r,g,b);
		    	
		    	result.setRgb(rgb);
		    	float[] hsb = {rs.getFloat("hue"), 1, rs.getFloat("brightness")};
		    	result.setHsbVal(hsb);
		    	
		    	result.setId(rs.getInt("id"));
				result.setName(rs.getString("colorname"));
				System.out.println("Color: " + rs.getString("colorname"));
				result.setUserId(rs.getInt("user_id"));
		    	list.add(result);
		    }
		    
		}
		catch(SQLException e){
			System.out.println(e.getMessage());
		}
	    finally
	    {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
		
		
		return list;
		
	}
	
	public int getColorAnswerCount(String name){
		int count =0;
		
		try {
			ResultSet rs = null;
			
			conn = DriverManager.getConnection(DB_URL);
			
			String stmt = "select count(id) from answers where colorname = ?";
			PreparedStatement statement = conn.prepareStatement(stmt);
		    statement.setString(1, name);
		    
		    rs = statement.executeQuery();
		    
		    while(rs.next()){
		    	count = rs.getInt("count(id)");
		    	System.out.println("count: " + rs.getInt("count(id)"));
		    }
		    
		}
		catch(SQLException e){
			System.out.println(e.getMessage());
		}
	    finally
	    {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
		
		return count;
	}
	
	/**
	 * 
	 * @param hMin
	 * @param hMax
	 * @param bMin
	 * @param bMax
	 * @param filter[0] 0=no gender, 1=male, 2=female
	 * @param filter[1] 0=no filter, 1=colorblind, 2=non colorblind
	 * @return
	 */
	public ArrayList<ColorCount> getColorNameCountList(float hMin, float hMax, float bMin, float bMax, int[] filter){
		
		ArrayList<ColorCount> list = new ArrayList<ColorCount>();
		
		try {
			ResultSet rs = null;
			
			conn = DriverManager.getConnection(DB_URL);
			
			String stmt;
			StringBuffer sb = new StringBuffer();
			
			if((filter[0] == 0) && (filter[1] == 0)){
				sb.append("select colorname , count(id) from answers where hue >= ? and hue <= ? and brightness >= ? and brightness <= ? group by colorname order by count(id) desc");
			}
			else{
				sb.append("select answers.colorname, count(answers.id), users.ychrom from answers inner join users on answers.user_id=users.id " + 
						"where answers.hue >= ? and answers.hue <= ? and answers.brightness >= ? and answers.brightness <= ? ");
				if(filter[0] > 0){
					sb.append("and users.ychrom = ? ");
				}
				if(filter[1] > 0){
					sb.append("and users.colorblind = ? ");
				}
				sb.append("group by colorname order by count(answers.id) desc");
			}
			stmt = sb.toString();
			
			PreparedStatement statement = conn.prepareStatement(stmt);
			
			statement.setFloat(1, hMin);
		    statement.setFloat(2, hMax);
		    statement.setFloat(3, bMin);
		    statement.setFloat(4, bMax);
		    
		    int param = 5;
		    
		    if(filter[0] == 1){
		    	statement.setInt(5, 1);
		    	param++;
		    }
		    else if (filter[0] == 2){
		    	statement.setInt(5, 0);
		    	param++;
		    }
		    
		    if(filter[1] == 1){
		    	statement.setInt(param, 1);
		    }
		    else if (filter[1] == 2){
		    	statement.setInt(param, 0);
		    }
		    
		    rs = statement.executeQuery();
		    if(filter[0] == 0 && filter[1] == 0){
			    while(rs.next()){
			    	ColorCount temp = new ColorCount(rs.getString("colorname"), rs.getInt("count(id)"), -1);
			    	list.add(temp);
			    }
		    }
		    else {
		    	while(rs.next()){
			    	
			    	ColorCount temp = new ColorCount(rs.getString("colorname"), rs.getInt("count(answers.id)"), rs.getInt("ychrom"));
			    	list.add(temp);
			    }
		    }
		    
		    
		}
		catch(SQLException e){
			System.out.println(e.getMessage());
		}
	    finally
	    {
	      try
	      {
	        if(conn != null)
	          conn.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
		
		return list;
	}
	
	private String sanitize(String src) {
		
		StringBuffer temp = new StringBuffer();
		String[] split = src.split("\"");
		for(int i = 0; i < split.length; i++){
			temp.append(split[i]);
		}
		src = temp.toString();
		split = src.split("\'");
		temp = new StringBuffer();
		for(int i = 0; i < split.length; i++){
			temp.append(split[i]);
		}
		return temp.toString();
	}
	
	private float min(float i1, float i2){
		if(i1<i2){
			return i1;
		}
		else{
			return i2;
		}
	}
	
	private float max(float i1, float i2){
		if(i1>i2){
			return i1;
		}
		else{
			return i2;
		}
	}
	
	
	public static void main (String[] args){
//	
			DbHandler db = new DbHandler();
//			
//			db.getColorAnswerCount("p");
//			
//			float[] c1 = { 0 , 1, 1};
//			float[] c2 = {250, 1, 1};
//			
//			ArrayList<ColorCount> res = db.getColorNameCountList(0, 250, 1, 1, 2);
//			
//			for(int i = 0; i<res.size(); i++){
//				System.out.println( res.get(i).getName() + " "+ res.get(i).getCount() + " " + res.get(i).getYchrom());
//			}
//			
//			db.getRangeColorData(c1, c2);
//			
//			float[] temp = Color.RGBtoHSB(100, 100, 0, null);
//			ArrayList<AnswerData> res = db.getOneColorData(temp[0], 1, temp[2]);
//			
//			for(int i = 0; i < res.size(); i++){
//				db.getOneUserData(res.get(0));
//			}
		
	}
	
}
